package cn.edu.sanxiau.www.dao.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.sanxiau.www.dao.ClassRoomDao;
import cn.edu.sanxiau.www.model.ClassRoom;
import cn.edu.sanxiau.www.model.PaiKe;
import cn.edu.sanxiau.www.model.PaiRoom;
import cn.edu.sanxiau.www.model.RoomCourse;
import cn.edu.sanxiau.www.model.User2;
import frame.utils.dbutil.JdbcUtils;

public class ClassRoomDaoImpl implements ClassRoomDao {

	@Override
	public List<ClassRoom> queryAllClassroom() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM classroom";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			List<ClassRoom> classroom_db_list = new ArrayList<ClassRoom>();
			
			while (rs.next()) {
				ClassRoom classroom_db = new ClassRoom();
				classroom_db.setRoomId(rs.getInt("roomId"));
				classroom_db.setRoomName(rs.getString("roomName"));
				classroom_db.setCapacity(rs.getString("capacity"));
				classroom_db.setState(rs.getString("state"));
				
				classroom_db_list.add(classroom_db);
				
			}
			
			return classroom_db_list;
			
		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int addClassRoomByClassRoomr(ClassRoom classroom) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "INSERT INTO  classroom (roomId,roomName,capacity,state) VALUES(?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, classroom.getRoomId());
			pstmt.setString(2, classroom.getRoomName());
			pstmt.setString(3, classroom.getCapacity());
			pstmt.setString(4, classroom.getState());

			int m = pstmt.executeUpdate();
			return m;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int deleteClassRoomByClassRoomId(int roomId) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = JdbcUtils.getConnection();

			String sql = "DELETE FROM classroom WHERE roomId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, roomId);

			int m = pstmt.executeUpdate();
			return m;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public ClassRoom queryClassRoomByClassRoomId(int roomId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM classroom WHERE roomId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, roomId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				ClassRoom classroom_db = new ClassRoom();
				classroom_db.setRoomId(rs.getInt("roomId"));
				classroom_db.setRoomName(rs.getString("roomName"));
				classroom_db.setCapacity(rs.getString("capacity"));
				classroom_db.setState(rs.getString("state"));
				return classroom_db;
			}
		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int updateClassRoomByClassRoom(ClassRoom classroom) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = JdbcUtils.getConnection();
			String sql = "UPDATE classroom SET roomName=?,capacity=?,state=? WHERE roomId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, classroom.getRoomName());
			pstmt.setString(2, classroom.getCapacity());
			pstmt.setString(3, classroom.getState());
			pstmt.setInt(4, classroom.getRoomId());

			int m = pstmt.executeUpdate();
			return m;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public int queryClassRoomTotalRecord() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT count(*) FROM  classroom";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				int count = rs.getInt(1);
				return count;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public List<ClassRoom> queryCurrentPageDataList(int i, int ps) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM  classroom LIMIT ?,?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, i);
			pstmt.setInt(2, ps);
			rs = pstmt.executeQuery();
			List<ClassRoom> classroomList = new ArrayList<>();
			while (rs.next()) {
				ClassRoom classroom_db = new ClassRoom();
				classroom_db.setRoomId(rs.getInt("roomId"));
				classroom_db.setRoomName(rs.getString("roomName"));
				classroom_db.setCapacity(rs.getString("capacity"));
				classroom_db.setState(rs.getString("state"));
				classroomList.add(classroom_db);
			}
			return classroomList;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	
	
	
	
	
	//////////////////////////////////////////////////////
	@Override
	public List<PaiKe> queryAllCourse() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT classroom.roomId, classroom.roomName, classroom.capacity, classroom.state, room_course.date, room_course.time FROM classroom INNER JOIN room_course ON classroom.roomId = room_course.roomId";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			List<PaiKe> paike_db_list = new ArrayList<PaiKe>();

			while (rs.next()) {
				PaiKe paike_db = new PaiKe();
				paike_db.setRoomId(rs.getInt("classroom.roomId"));
				paike_db.setRoomName(rs.getString("classroom.roomName"));
				paike_db.setCapacity(rs.getString("classroom.capacity"));
				paike_db.setState(rs.getString("classroom.state"));
				paike_db.setDate(rs.getString("room_course.date"));
				paike_db.setTime(rs.getString("room_course.time"));

				paike_db_list.add(paike_db);
			}

			return paike_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<ClassRoom> queryClassRoomByCourseId(int courseId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT classroom.roomName, classroom.roomId, classroom.capacity, classroom.state FROM course INNER JOIN room_course ON course.courseId = room_course.courseId INNER JOIN classroom ON room_course.roomId = classroom.roomId where courseId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, courseId);
			rs = pstmt.executeQuery();

			List<ClassRoom> classRoom_db_list = new ArrayList<ClassRoom>();

			while (rs.next()) {
				ClassRoom classRoom_db = new ClassRoom();
				classRoom_db.setRoomId(rs.getInt("roomId"));
				classRoom_db.setRoomName(rs.getString("roomName"));
				classRoom_db.setCapacity(rs.getString("capacity"));
				classRoom_db.setState(rs.getString("state"));

				classRoom_db_list.add(classRoom_db);
			}

			return classRoom_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public String queryRoomNameByCourseId(int courseId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT classroom.roomName FROM (classroom , course) INNER JOIN room_course ON course.courseId = room_course.courseId AND room_course.roomId = classroom.roomId where course.courseId=";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, courseId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				String roomName = rs.getCursorName();
				
				return roomName;
			}
		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int updateRoomStateByRoomId(int roomId) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = JdbcUtils.getConnection();
			String sql = "UPDATE classroom SET date=?,state=? WHERE roomId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "0");
			pstmt.setString(2, "0");
			pstmt.setInt(3, roomId);

			int m = pstmt.executeUpdate();
			return m;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	/* (non-Javadoc)
	 * @see cn.edu.sanxiau.www.dao.ClassRoomDao#queryRoomByRoomId(int)
	 */
	@Override
	public List<PaiKe> queryAllRoomByRoomId(int roomId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT room_course_Id, room_course.courseId, room_course.time, room_course.date, classroom.capacity, classroom.roomName, classroom.roomId FROM room_course INNER JOIN classroom ON classroom.roomId = room_course.roomId where room_course.roomId = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, roomId);
			rs = pstmt.executeQuery();

			List<PaiKe> paike_db_list = new ArrayList<PaiKe>();

			while (rs.next()) {
				PaiKe paike_db = new PaiKe();
				paike_db.setRoomId(rs.getInt("classroom.roomId"));
				paike_db.setRoomCourseId(rs.getInt("room_course_Id"));
				paike_db.setRoomName(rs.getString("classroom.roomName"));
				paike_db.setDate(rs.getString("room_course.date"));
				paike_db.setTime(rs.getString("room_course.time"));
				paike_db.setCapacity(rs.getString("classroom.capacity"));
				paike_db.setCourseId(rs.getInt("room_course.courseId"));

				paike_db_list.add(paike_db);
			}

			return paike_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<PaiKe> queryAllClassRoom() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * from classroom";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			List<PaiKe> paike_db_list = new ArrayList<PaiKe>();

			while (rs.next()) {
				PaiKe paike_db = new PaiKe();
				paike_db.setRoomId(rs.getInt("roomId"));
				paike_db.setRoomName(rs.getString("roomName"));
				paike_db.setCapacity(rs.getString("capacity"));
				paike_db.setRdate(rs.getString("state"));
				paike_db.setDate(rs.getString("date"));

				paike_db_list.add(paike_db);
			}

			return paike_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
//////////////////////////////addUI/////////////////////////////////////
	@Override
	public List<PaiRoom> queryAllExam() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT classroom.roomId, classroom.roomName, classroom.state, classroom.capacity, classroom.date, room_exam.date, room_exam.time FROM room_exam INNER JOIN classroom ON classroom.roomId = room_exam.roomld";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			List<PaiRoom> pairoom_db_list = new ArrayList<PaiRoom>();

			while (rs.next()) {
				PaiRoom pairoom_db = new PaiRoom();
				pairoom_db.setRoomId(rs.getInt("classroom.roomId"));
				pairoom_db.setRoomName(rs.getString("classroom.roomName"));
				pairoom_db.setCapacity(rs.getString("classroom.capacity"));
				pairoom_db.setExamState(rs.getString("classroom.state"));
				pairoom_db.setDate(rs.getString("room_exam.date"));
				pairoom_db.setTime(rs.getString("room_exam.time"));

				pairoom_db_list.add(pairoom_db);
			}

			return pairoom_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<PaiRoom> queryAllClassRoom2() {        //queryEmputyRoom
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * from classroom";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			List<PaiRoom> pairoom_db_list = new ArrayList<PaiRoom>();

			while (rs.next()) {
				PaiRoom pairoom_db = new PaiRoom();
				pairoom_db.setRoomId(rs.getInt("roomId"));
				pairoom_db.setRoomName(rs.getString("roomName"));
				pairoom_db.setCapacity(rs.getString("capacity"));
				pairoom_db.setRdate(rs.getString("state"));
				pairoom_db.setDate(rs.getString("date"));

				pairoom_db_list.add(pairoom_db);
			}

			return pairoom_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<PaiRoom> queryAllRoomByRoomId2(int roomId) {      //queryEmputyRoom
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT classroom.capacity, classroom.roomName, classroom.roomId, room_exam.room_exam_id, room_exam.examId, room_exam.date, room_exam.time FROM classroom INNER JOIN room_exam ON room_exam.roomld = classroom.roomId = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, roomId);
			rs = pstmt.executeQuery();

			List<PaiRoom> pairoom_db_list = new ArrayList<PaiRoom>();

			while (rs.next()) {
				PaiRoom pairoom_db = new PaiRoom();
				pairoom_db.setCapacity(rs.getString("classroom.capacity"));
				pairoom_db.setRoomName(rs.getString("classroom.roomName"));
				pairoom_db.setRoomId(rs.getInt("classroom.roomId"));
				
				pairoom_db.setRoomExamId(rs.getInt("room_exam_id"));
			
				pairoom_db.setDate(rs.getString("room_exam.date"));
				pairoom_db.setTime(rs.getString("room_exam.time"));
				
				pairoom_db.setRoomExamId(rs.getInt("room_exam.examId"));

				pairoom_db_list.add(pairoom_db);
			}

			return pairoom_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;

	}
}
